import requests
from django.shortcuts import render

# Create your views here.
from django.http import JsonResponse
from django.db.models import Q
from zentao.settings import PYMYSQL_CONF
from rest_framework import viewsets, status
from rest_framework.exceptions import AuthenticationFailed
from rest_framework.response import Response
from django.core.paginator import Paginator
from django.http import HttpResponse
from django.utils.encoding import iri_to_uri

from datetime import datetime, timedelta
from datetime import timezone
import pandas as pd
import pymysql
import numpy as np
import json
from openpyxl.styles import Alignment
from openpyxl.styles.borders import Border, Side
import traceback
import re

from apps.homepage.models import ZtAction, ZtTask, ZtBug, ZtDept,ZtStory
from .models import ZtRdEfficiency, ZtRdEfficiencyRule
from .serializers import ZtRdEfficiencySerializer

from .general_task_done import general_task_done
from .not_close_bug import not_close_bug
from .submmit_bug import submmit_bug
from .bug_close_percent import bug_close_percent

from zentao.settings import STATISTIC_PERSON, CODE_ACCOUNT_MAP, CODE_URL

import warnings
warnings.filterwarnings('ignore')

#设置表格打印宽度
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.width', 180)  # 设置打印宽度(**重要**)

import time
from functools import wraps

def timing_decorator(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        print(f"Function {func.__name__!r} executed in {(end_time - start_time)*1000:.2f}ms")
        return result
    return wrapper

#判断是否登录
def has_permission(request):
    # print(request.session.get('token'), request.META.get('HTTP_TOKEN'))

    # session没有token，则未登录
    session_token = request.session.get('token')
    if not session_token:
        return False

    # 如果未传入token，则未登录
    request_token = request.META.get('HTTP_TOKEN')
    if not request_token:
        return False

    #如果都传入，则比对是否一致
    if session_token == request_token:
        return True
    else:
        return False

#查找所有用户
def get_user():


    #临时过滤外围人员，统计人员的等级
    df = pd.read_excel(STATISTIC_PERSON)
    names = []
    name_special = []
    name2level = {}
    for i, j, k in zip(df['拟供应人员姓名'], df['单位（部门）'], df['等级']):
        names.append(j + '-' + i)
        name2level[j + '-' + i] = k

        if j == '计划财务部':
            name_special.append(i)

    sql = '''
    SELECT 
        u.account,
        u.realname,
        c.name AS company,
        d.name AS dept,
        u.role
    FROM 
        zt_user AS u
    LEFT JOIN 
        zt_company AS c ON u.company = c.id
    LEFT JOIN 
        zt_dept AS d ON u.dept = d.id
    WHERE 
        u.deleted = '0'
    '''
    # pymysql连接数据
    connection = pymysql.connect(**PYMYSQL_CONF)
    # 执行 SQL 语句
    # 连接数据库
    with connection.cursor() as cursor:
        cursor.execute(sql)
        get_users = cursor.fetchall()

    #将岗位转为岗位类型
    position_map = {'开发': 'develop',
                    '开发负责人': 'develop',
                    '开发组长': 'develop',
                    '测试': 'test',
                    '测试负责人': 'test',
                    '测试组长': 'test',
                    '需求': 'story',
                    '需求负责人': 'story',
                    '需求组长': 'story',
                    '运维': 'om',
                    '实施': 'implement',
                    '咨询人员': 'ask',
                    '综合人员': 'ask'}
    position_en2zh_map = {
        "imp":"实施",
        "om": "运维",
        "cp": "综合人员",
        "cst": "咨询人员",
        "qaz": "测试组长",
        "dez": "开发组长",
        "rez": "需求组长",
        "dev": "开发",
        "qa": "测试",
        "td": "开发负责人",
        "pd": "需求负责人",
        "qd": "测试负责人",
        "req": "需求"
    }

    users = []
    account2level = {}

    for user in get_users:

        #临时过滤外围人员
        name = user[1].replace('（', '(').replace(' ', '').split('(')[0]

        if name not in name_special:
            name = f'{user[3]}-{name}'

            if name not in names:
                continue
        else:
            name = f'计划财务部-{name}'

        account2level[user[0]] = name2level[name]

        #这几类权限不要
        if user[4] not in position_en2zh_map.keys():
            continue
        if user[1] in ['test_测试','test_测试开发主管','test_测试经理']:
            continue
        #没有部门
        if not user[3]:
            continue

        # users.append(list(user))
        users.append({
            'account': user[0],
            'realname': user[1],
            'company': user[2],
            'dept': user[3],
            'name': position_en2zh_map[user[4]],
            'positionType': position_map[position_en2zh_map[user[4]]]
        })

    connection.close()

    # df = pd.DataFrame(columns=['account', 'realname', 'company', 'dept', 'name','positionType'], data=users)
    df = pd.DataFrame(users)

    return users, account2level, df

# get_user()

#查找当月需求的操作记录
def get_story_action(start_date, current_time):

    #有产品和项目的需求id
    query = Q()
    query &= (Q(projectStory_story__project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
    # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
    query &= Q(deleted='0')  # 不删除的

    story_ids = ZtStory.objects.filter(query).values_list('id').distinct()

    # 搜索条件,初始化一个空的查询集
    query = Q()
    query &= Q(date__range=(start_date, current_time))  # 当月操作日期
    query &= Q(objectType='story')  # 只要需求相关的
    query &= Q(objectID__in=story_ids)  # 只要项目和产品不能都被删除的需求

    actions = ZtAction.objects.filter(query).values('objectType','actor','action')

    #转为df
    data = []
    for action in actions:
        data.append([action['objectType'], action['actor'], action['action']])

    df = pd.DataFrame(columns=['objectType','actor','action'], data=data)

    # df.to_csv('story.csv',index=False)

    return data, df

#查找所有未完成的任务
def get_nocomplete_task(start_date, current_time):

    #筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
    # 搜索条件,初始化一个空的查询集
    query = Q()
    query &= (Q(project__projectProduct_project__product__deleted='0') | Q(project__deleted='0'))  # 项目和产品不能都被删除
    # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
    query &= Q(deleted='0')  # 不删除的
    # query &= Q(mode__in=['multi','linear'])  # 多人任务

    tasks = ZtTask.objects.filter(query).exclude(parent=-1).select_related('assignedTo').prefetch_related('taskteam_task','taskteam_task__account').distinct()

    #转为df
    data = []
    for task in tasks:

        # 如果没有指派时间，则用创建时间替代
        # if not task.assignedDate:
        #     assignedDate = task.openedDate.astimezone(timezone.utc).replace(tzinfo=None)
        # else:
        #     assignedDate = task.assignedDate.astimezone(timezone.utc).replace(tzinfo=None)

        #不为当前时间的只要未完成的
        # if assignedDate < start_date or assignedDate > current_time:
        # 获取指派人，如果是多人任务则拼接，否则使用指派人
        team_list = task.taskteam_task.all()  # 获取该任务所有人
        if team_list:
            for person in team_list:

                # 只有不完成的填进去
                if person.status != 'done':
                    # data.append([task.id, person.account.account,assignedDate])
                    data.append([person.account.account])
        else:
            if task.assignedTo:
                assignedTo = task.assignedTo.account
            else:
                assignedTo = ''
            # 只有不完成的填进去
            if task.status in ['pause','wait','doing']:
                if assignedTo == 'sunzhao':
                    print(',,,,', task.id)
                data.append([assignedTo])

        # else:
        #
        #     #获取指派人，如果是多人任务则拼接，否则使用指派人
        #     team_list = task.taskteam_task.all()  # 获取该任务所有人
        #     if team_list:
        #         for person in team_list:
        #
        #             # data.append([task.id,person.account.account,assignedDate])
        #             data.append([person.account.account])
        #     else:
        #         if task.assignedTo:
        #             assignedTo = task.assignedTo.account
        #         else:
        #             assignedTo = ''
        #         data.append([assignedTo])

    # # 查找父节点的任务，不需要
    # # 搜索条件,初始化一个空的查询集
    # query = Q()
    # query &= Q(deleted='0')  # 不删除的
    # query &= Q(parent=-1)  # 父节点
    #
    # parent_tasks = ZtTask.objects.filter(query).values_list('id').distinct()
    #
    # # 单人任务使用action筛选，只要最后一个指派人
    # query = Q()
    # # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
    # query &= Q(objectType='task')  # 不删除的
    # query &= Q(action='assigned')  # 多人任务
    #
    # actions = ZtAction.objects.filter(query).exclude(objectID__in=parent_tasks).values('objectID', 'extra',
    #                                                                                    'date')
    # task_single = {}
    # for action in actions:
    #
    #     assignedDate = action['date'].astimezone(timezone.utc).replace(tzinfo=None)
    #
    #     # 不为当前时间的只要未完成的
    #     if assignedDate < start_date or assignedDate > current_time:
    #         if action['objectID'] not in task_ids:
    #             task_single[action['objectID']] = [action['extra']]
    #             # task_single[action['objectID']] = [action['objectID'], action['extra'],assignedDate]
    #
    #     else:
    #         task_single[action['objectID']] = [action['extra']]
    #         # task_single[action['objectID']] = [action['objectID'], action['extra'], assignedDate]
    #
    # data += task_single.values()

    # df = pd.DataFrame(columns=['id','assignedTo','assignedDate'], data=data)
    df = pd.DataFrame(columns=['assignedTo'], data=data)
    df[['assignedTo']] = df[['assignedTo']].fillna('').astype(str)

    # df.to_csv('task.csv',index=False)

    return data, df

#查找当月的完成任务
# def get_finish_task(start_date, current_time):
#
#     #筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
#     # 搜索条件,初始化一个空的查询集
#     query = Q()
#     # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
#     query &= Q(deleted='0')  # 不删除的
#     query &= Q(mode__in=['multi','linear'])  # 多人任务
#
#     tasks = ZtTask.objects.filter(query).exclude(parent=-1).select_related('assignedTo').prefetch_related('taskteam_task','taskteam_task__account').distinct()
#
#     #转为df
#     task_accounts = {}   #多人任务人员列表
#     for task in tasks:
#
#         #获取指派人，如果是多人任务则拼接，否则使用指派人
#         team_list = task.taskteam_task.all()  # 获取该任务所有人
#         if team_list:
#             task_accounts[task.id] = []
#             for person in team_list:
#
#                 # data.append([task.id,person.account.account,assignedDate])
#                 task_accounts[task.id].append(person.account.account)
#
#     #查找父节点的任务，不需要
#     # 搜索条件,初始化一个空的查询集
#     query = (Q(deleted='1') | Q(parent=-1) | (Q(project__projectProduct_project__product__deleted='1') & Q(project__deleted='1')))  # 父节点或者项目和产品都被删除或者删除的
#
#     parent_tasks = ZtTask.objects.filter(query).values_list('id').distinct()
#
#     # 搜索条件,初始化一个空的查询集
#     query = Q()
#     query &= Q(date__range=(start_date, current_time))  # 当月操作日期
#     query &= Q(objectType='task')  # 只要任务相关的
#     query &= Q(action='finished')  # 只要完成的
#
#     actions = ZtAction.objects.filter(query).exclude(objectID__in=parent_tasks).values('objectID', 'actor')
#     # actions = ZtAction.objects.filter(query).values('objectID', 'actor','date')
#
#     #统计完成人和任务
#     # data = []
#     finish_task = {}
#
#     for action in actions:
#
#         #如果是多人任务，判断是否在团队成员中，在的话才算完成，否则直接统计
#         if action['objectID'] in task_accounts.keys():
#             if action['actor'] in task_accounts[action['objectID']]:
#                 finish_task[f"{action['objectID']}{action['actor']}"] = [action['objectID'],action['actor']]
#         else:
#             finish_task[action['objectID']] = [action['objectID'],action['actor']]
#
#     df_action = pd.DataFrame(columns=['id','finishedBy'],data=finish_task.values())
#
#     # 搜索条件,初始化一个空的查询集
#     task_ids = list(df_action['id'])
#     query = Q()
#     # query &= Q(finishedDate__range=(start_date, current_time))  # 当月指派时间
#     query &= Q(id__in=task_ids)  # 在完成人任务id里的
#     query &= Q(deleted='0')  # 不删除的
#
#     tasks = ZtTask.objects.filter(query).exclude(parent=-1).select_related('finishedBy').prefetch_related('taskteam_task','taskteam_task__account').distinct()
#
#     #转为df
#     data = []
#     for task in tasks:
#
#         # 获取指派人，如果是多人任务则拼接，否则使用指派人
#         team_list = task.taskteam_task.all()  # 获取该任务所有人
#         if team_list:
#             for person in team_list:
#                 data.append([task.id, person.account.account, str(task.pri), task.difficulty, person.consumed])
#         else:
#             if task.finishedBy:
#                 finishedBy = task.finishedBy.account
#             else:
#                 finishedBy = ''
#             data.append([task.id, finishedBy,str(task.pri), task.difficulty, task.consumed])
#     #完成人所在任务对应的所有人的工时、优先级、难度系数
#     df_task = pd.DataFrame(columns=['id','finishedBy','pri','difficulty','consumed'], data=data)
#
#     #合并
#     df = pd.merge(df_action, df_task, on=['id','finishedBy'], how='left')
#     # 删除列
#     df = df.drop(['id'], axis=1)
#     df[['finishedBy','pri','difficulty']] = df[['finishedBy','pri','difficulty']].fillna('').astype(str)
#
#     df['consumed'] = df['consumed'].fillna('').astype(str)
#     # 使用 to_dict 方法，设置 orient='records' 来得到每一行作为一个字典的列表
#     tasks = df.to_dict(orient='records')
#
#     df.to_csv('finish.csv', index=False)
#
#     return tasks, df

def get_finish_task(start_date, current_time):
    # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
    # 搜索条件,初始化一个空的查询集
    query = Q()
    query &= (Q(project__projectProduct_project__product__deleted='0') | Q(project__deleted='0'))  # 项目和产品不能都被删除
    # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
    query &= Q(deleted='0')  # 不删除的
    # query &= Q(mode__in=['multi','linear'])  # 多人任务

    tasks = ZtTask.objects.filter(query).exclude(parent=-1).select_related('assignedTo','finishedBy').prefetch_related(
        'taskteam_task', 'taskteam_task__account').distinct()

    # 转为df
    finishedBy = {} #任务完成
    need_date_finishedBy = {}   #需要添加date的任务
    need_date_task_ids = [] #需要添加date的任务id
    multi_task_finishedDate = {}    #多人任务的任务完成日期

    for task in tasks:

        if task.pri not in [1,2,3,4]:
            task.pri = 3

        #如果任务状态为cancel，则跳过
        if task.status == 'cancel':
            continue

        # 获取所有完成人的任务，其中多人任务key为id+account，单人任务key为id
        # 获取指派人，如果是多人任务则拼接，否则使用指派人
        team_list = task.taskteam_task.all()  # 获取该任务所有人
        if team_list:

            # 多人任务如果有完成时间，则放入
            if task.status in ['done', 'closed'] and task.finishedDate:
                multi_task_finishedDate[task.id] = task.finishedDate
            else:
                multi_task_finishedDate[task.id] = None

            for person in team_list:

                # 只有完成的填进去
                if person.status == 'done':
                    need_date_task_ids.append(task.id)
                    need_date_finishedBy[str(task.id)+person.account.account]={'finishedBy':person.account.account,'pri':task.pri,
                                                                               'difficulty':task.difficulty,'consumed':person.consumed}
        else:

            #有完成人，并且状态为完成和关闭
            if task.finishedBy and task.status in ['done','closed']:

                if task.finishedDate:
                    finishedBy[str(task.id)]={'finishedBy':task.finishedBy.account,'pri':task.pri,
                                              'difficulty':task.difficulty,'consumed':task.consumed,
                                              'date':task.finishedDate}
                else:
                    need_date_finishedBy[str(task.id)]={'finishedBy':task.finishedBy.account,'pri':task.pri,
                                              'difficulty':task.difficulty,'consumed':task.consumed}
                    need_date_task_ids.append(task.id)

    # 搜索条件,初始化一个空的查询集
    query = Q()
    # query &= Q(date__range=(start_date, current_time))  # 当月操作日期
    query &= Q(objectType='task')  # 只要任务相关的
    query &= Q(action='finished')  # 只要任务完成的
    query &= Q(objectID__in=need_date_task_ids) #只要完成任务的

    actions = ZtAction.objects.filter(query).values('objectID', 'actor', 'action','date')
    # actions = ZtAction.objects.filter(query).values('objectID', 'actor','date')

    #统计完成人和任务
    # data = []
    finish_task = {}
    s = {}

    for action in actions:

        #id和account拼接
        id_account = str(action['objectID'])+action['actor']

        #记录多人任务的完成时间，以最后一个成员完成时间为准
        if action['objectID'] in multi_task_finishedDate.keys() and multi_task_finishedDate[action['objectID']] is None:
            multi_task_finishedDate[action['objectID']] = action['date']

        # s[str(action['objectID'])] = action['date']
        # s[str(action['objectID'])+action['actor']] = action['date']
        if str(action['objectID']) in need_date_finishedBy.keys():
            mid = need_date_finishedBy.pop(str(action['objectID']))
            mid['date'] = action['date']
            finishedBy[str(action['objectID'])] = mid
            # print(action['objectID'],action['actor'],action['action'],action['date'])

        if id_account in need_date_finishedBy.keys():
            mid = need_date_finishedBy.pop(id_account)
            mid['date'] = action['date']
            finishedBy[id_account] = mid
            # print(str(action['objectID'])+action['actor'],action['action'],action['date'])
        #
        # print(action['objectID'],action['actor'],action['action'],action['date'],'##############################')
    #为没有完成时间的多人任务添加完成时间
    for key,value in need_date_finishedBy.items():
        task_id = int(re.findall(r'\d+', key)[0])

        #如果id在多人任务完成时间内，则使用该时间
        if task_id in multi_task_finishedDate.keys():
            need_date_finishedBy[key]['date'] = multi_task_finishedDate[task_id]

    #获取有完成时间的任务
    for key, value in need_date_finishedBy.items():
        if 'date' in value.keys() and value['date'] is not None:
            finishedBy[key] = value

    #对完成任务进行筛选
    filter_finished_tasks = []
    for key,value in finishedBy.items():
        if  start_date<=value['date'].astimezone(timezone.utc).replace(tzinfo=None)<=current_time:
            filter_finished_tasks.append(value)

    #转df
    df = pd.DataFrame(filter_finished_tasks)

    # 删除列
    df = df.drop(['date'], axis=1)

    df[['finishedBy','pri','difficulty']] = df[['finishedBy','pri','difficulty']].fillna('').astype(str)
    df['consumed'] = df['consumed'].fillna('').astype(str)
    # 使用 to_dict 方法，设置 orient='records' 来得到每一行作为一个字典的列表
    tasks = df.to_dict(orient='records')

    # df.to_csv('完成任务.csv')

    return tasks, df

# # 获取当前时间
# now = datetime.now()
#
# # 计算当月1号00:00:00的时间
# start_date = datetime(now.year, now.month, 1)
# st = time.time()
# get_finish_task(start_date,now)
# print(time.time()-st)

#查找当月提交的bug（缺陷）
def get_bug(start_date, current_time):

    # 搜索条件,初始化一个空的查询集
    query = Q()
    query &= Q(openedDate__range=(start_date, current_time))  # 当月
    query &= (Q(project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
    query &= Q(deleted='0')  # 不删除的

    bugs = ZtBug.objects.filter(query).values('openedBy','assignedTo','resolvedBy','closedBy')

    #转为df
    data = []
    for bug in bugs:
        data.append([bug['openedBy'],bug['assignedTo'], bug['resolvedBy'], bug['closedBy']])

    df = pd.DataFrame(columns=['openedBy','assignedTo','resolvedBy','closedBy'],data=data)
    # df.to_csv('bug.csv',index=False)

    return bugs, df

#查找所有未关闭的bug（缺陷）
def get_noclose_bug(start_date, current_time):

    #提交
    # 搜索条件,初始化一个空的查询集
    query = Q()
    # query &= Q(openedDate__range=(start_date, current_time))  # 当月
    query &= (Q(project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
    query &= Q(deleted='0')  # 不删除的

    bugs = ZtBug.objects.filter(query).values('openedDate','openedBy','status')

    #转为df
    data = []
    for bug in bugs:

        # 时间转换
        #如果没有创建时间，跳过
        if not bug['openedDate']:
            continue
        openedDate = bug['openedDate'].astimezone(timezone.utc).replace(tzinfo=None)

        #不为当前时间的只要未关闭的
        if openedDate < start_date or openedDate > current_time:
            if bug['status'] != 'closed':
                data.append([bug['openedBy']])
                # pass
        else:
            data.append([bug['openedBy']])

    df = pd.DataFrame(columns=['openedBy'],data=data)
    # df.to_csv('bug.csv',index=False)

    #完成
    # 搜索条件,初始化一个空的查询集
    query = Q()
    query &= Q(closedDate__range=(start_date, current_time))  # 当月
    query &= Q(deleted='0')  # 不删除的

    bugs = ZtBug.objects.filter(query).values('closedBy')

    #转为df
    data = []
    for bug in bugs:
        data.append([bug['closedBy']])

    df_close = pd.DataFrame(columns=['closedBy'],data=data)

    df['closedBy'] = df_close['closedBy']

    df_all = df_close.rename(columns={'closedBy':"openedBy"})
    df_all['closedBy'] = ''

    df = pd.concat([df,df_all])

    # df.to_csv('ceshi.csv',index=False)

    # 使用 to_dict 方法，设置 orient='records' 来得到每一行作为一个字典的列表
    bugs = df.to_dict(orient='records')

    return bugs, df

# # 获取当前时间
# now = datetime.now()
#
# # 计算当月1号00:00:00的时间
# start_date = datetime(now.year, now.month, 1)
# get_noclose_bug(start_date,now)

#查找当月的指派bug（缺陷）
def get_assigned_bug(start_date, current_time):

    # 搜索条件,初始化一个空的查询集
    query = Q()
    query &= Q(assignedDate__range=(start_date, current_time))  # 当月
    query &= (Q(project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
    query &= Q(deleted='0')  # 不删除的

    bugs = ZtBug.objects.filter(query).values('openedBy','assignedTo','resolvedBy','closedBy')

    #转为df
    data = []
    for bug in bugs:
        data.append([bug['openedBy'],bug['assignedTo'], bug['resolvedBy'], bug['closedBy']])

    df = pd.DataFrame(columns=['openedBy','assignedTo','resolvedBy','closedBy'],data=data)
    # df.to_csv('bug.csv',index=False)

    return bugs, df

#计算任务完成率
def cal_task_done_percent(df_nocomplete_task, df_finish_task):

    #将df_finish_task中的finishedBy添加到df_task
    # df_nocomplete_task['finishedBy'] = df_finish_task['finishedBy']
    df_nocomplete_task = pd.concat([df_nocomplete_task, df_finish_task], axis=1)

    # 计算每个人的任务指派数
    assigned_counts = df_nocomplete_task['assignedTo'].value_counts()
    # 计算每个人的任务完成数
    completed_counts = df_nocomplete_task['finishedBy'].value_counts()

    # 将结果合并为一个DataFrame
    result_df = pd.DataFrame({
        '综合/咨询-任务未完成数': assigned_counts,
        '综合/咨询-任务完成数': completed_counts
    }).fillna(0).astype(int)

    # 如果需要计算完成率，可以添加一列
    result_df['综合/咨询-任务指派数'] = result_df['综合/咨询-任务未完成数']+result_df['综合/咨询-任务完成数']
    result_df['综合/咨询-任务完成率'] = result_df['综合/咨询-任务完成数'] / result_df['综合/咨询-任务指派数'] * 100
    result_df['综合/咨询-任务完成率-得分'] = result_df['综合/咨询-任务完成率']

    result_df = result_df.drop(['综合/咨询-任务未完成数'], axis=1)

    # 如果指派为0，则为1
    result_df['综合/咨询-任务完成率-得分'] = result_df['综合/咨询-任务完成率-得分'].replace(np.inf, 100)

    # 使用reset_index()方法将索引转换为列
    result_df = result_df.reset_index()

    # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
    result_df = result_df.rename(columns={'index': 'account'})

    return result_df

#计算问题缺陷数
def cal_bug_count(df_bug):
    # 计算每个人的录入数
    assigned_counts = df_bug['openedBy'].value_counts()

    # 将结果合并为一个DataFrame
    result_df = pd.DataFrame({
        '实施-问题缺陷数': assigned_counts,
    }).fillna(0).astype(int)

    # 如果需要计算完成率，可以添加一列
    result_df['实施-问题缺陷数-得分'] = result_df['实施-问题缺陷数'].map(lambda x : min(x * 25, 100))

    # 使用reset_index()方法将索引转换为列
    result_df = result_df.reset_index()

    # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
    result_df = result_df.rename(columns={'openedBy': 'account'})

    return result_df

#计算需求变更数
def cal_story_change(df_action):

    #计算分数的函数
    def calculate_score(count):
        if count == 0:
            return 100
        elif count == 1:
            return 75
        elif 2 <= count <= 3:
            return 50
        elif 4 <= count <= 5:
            return 25
        else:
            # 如果次数不在给定的范围内，可以根据需要返回一个默认值或进行其他处理
            return 0

    # 筛选出操作为'acton'的行
    df_changed = df_action[df_action['action'] == 'changed']

    # 计算每个人的需求变更数
    assigned_counts = df_changed['actor'].value_counts()

    # 将结果合并为一个DataFrame
    result_df = pd.DataFrame({
        '需求-需求变更数': assigned_counts,
    }).fillna(0).astype(int)

    # 如果需要计算完成率，可以添加一列
    result_df['需求-需求变更数-得分'] = result_df['需求-需求变更数'].map(calculate_score)

    # 使用reset_index()方法将索引转换为列
    result_df = result_df.reset_index()

    # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
    result_df = result_df.rename(columns={'actor': 'account'})

    return result_df

#计算活跃度(需求创建、评审数)的分数
def cal_story_create_review(df_action, df_user):

    '''
    df_action DataFrame: 用户操作表
    df_user DataFrame: 用户表
    return DataFrame: 计算得分后的表
    '''

    def test(rank,rank_max, counts):
        '''
        如果部门只有一个人，则为100分，两个人则为100、75分，三个人为100、75、50分，其他按照以下规则打分
        【100分】需求数排名前25%得100分
        【75分】25%<需求数排名≤50%得75分
        【50分】50%<需求数排名≤75%得50分
        【25分】需求数排名后25%得25分
        【0分】工作活跃度为0得0分

        rank int: 当前排名
        rank_max int: 部门内最大排名
        counts int: 需求创建、评审数
        return int: 分数
        '''

        if rank_max == 1:
            score = 100
        elif rank_max == 2:
            if rank == 1:
                score = 100
            else:
                score = 75
        elif rank_max == 3:
            if rank == 1:
                score = 100
            elif rank == 2:
                score = 75
            else:
                score = 50
        else:
            percent = rank/rank_max
            if percent <= 0.25:
                score = 100
            elif percent <= 0.5:
                score = 75
            elif percent <= 0.75:
                score = 50
            else:
                score = 25

        #如果需求创建、评审数为0，则直接0分
        if counts == 0:
            score = 0

        return score

    # 筛选出操作为'action'的行
    df_create_review = df_action[(df_action['action'] == 'reviewed') | (df_action['action'] == 'opened')]

    # 计算每个人的需求变更数
    counts = df_create_review['actor'].value_counts()

    # 将结果合并为一个DataFrame
    result_df = pd.DataFrame({
        '需求创建评审数': counts,
    }).fillna(0).astype(int)

    # 使用reset_index()方法将索引转换为列
    result_df = result_df.reset_index()

    # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
    result_df = result_df.rename(columns={'actor': 'account'})

    df = pd.merge(df_user, result_df, on='account', how='left')

    df['需求创建评审数'] = df['需求创建评审数'].fillna(0).astype(int)

    #筛选需求人员
    df_mid = df[(df['需求创建评审数']>0) & (df['positionType']=='story')]
    # df_mid = df[df['name'].isin(['需求', '需求负责人', '需求组长'])]

    # 在每个部门内按counts进行排名，相同值时排名相同
    df_mid['需求-工作活跃度-排名'] = df_mid.groupby('dept')['需求创建评审数'].rank(method='dense', ascending=False)
    df_mid['需求-工作活跃度-排名'] = df_mid['需求-工作活跃度-排名'].astype(int)

    # 计算每个部门的最大排名
    max_rank_per_group = df_mid.groupby('dept')['需求-工作活跃度-排名'].transform('max')
    # 将每个组的最大排名添加到DataFrame中
    df_mid['需求-工作活跃度-最大排名'] = max_rank_per_group

    df_mid['需求-工作活跃度-得分'] = df_mid.apply(lambda row: test(row['需求-工作活跃度-排名'], row['需求-工作活跃度-最大排名'], row['需求创建评审数']), axis=1)

    df = pd.merge(df, df_mid, on=['account','realname','company','dept','name','positionType','需求创建评审数'], how='left')

    #将每个组的最大排名+1后，放到需求创建评审数为0且name列为'需求','需求负责人','需求组长'的后面
    # 计算每个组的最大排名，然后加1
    max_rank_plus_one = df.groupby('dept')['需求-工作活跃度-排名'].transform('max') + 1
    # 找到满足条件的行：'需求创建评审数'为0且name列为'需求','需求负责人','需求组长'的后面
    condition = (df['需求创建评审数']==0) & (df['positionType']=='story')
    # 将最大排名加1的结果赋给满足条件的行的指定列
    df.loc[condition, '需求-工作活跃度-排名'] = max_rank_plus_one[condition]
    #得分为0
    df.loc[condition, '需求-工作活跃度-得分'] = 0
    #如果排名依旧为空，则代表该部门没有需求数，则排名均为1
    df.loc[(df['positionType']=='story') & (df['需求-工作活跃度-排名'].isna()), '需求-工作活跃度-排名'] = 1

    #更新最大排名列
    df['需求-工作活跃度-最大排名'] = df.groupby('dept')['需求-工作活跃度-排名'].transform('max')

    return df

#计算开发-代码情况得分
def develop_code(x):

    if x == 0:
        score = 0
    elif 0<x<0.5:
        score = 50 + 20 * x
    elif 0.5<=x<1:
        score = 60 + 20 * (x - 0.5)
    elif 1<=x<1.5:
        score = 70 + 20 * (x - 1)
    elif 1.5<=x<2:
        score = 80 + 40 * (x - 1.5)
    else:
        score = 100

    return score

#取汉字直到非汉字
def get_zh(name):

    result = ''

    for i in name:
        if '\u4e00' <= i <= '\u9fa5':
            result += i
        else:
            break

    return result

#将用户名的字母和姓名拼接
def pinjie(account, name):
    account = ''.join(re.findall(r'[a-zA-Z]', account))

    return account + get_zh(name)

#计算研发效能，只允许127.0.0.1本地调用
@timing_decorator
def cal_rd_efficiency(request):
    if request.method == 'GET':

        if request.META['REMOTE_ADDR'] not in ['127.0.0.1', '172.17.0.1']:
        # if request.META['REMOTE_ADDR'] != :
            return JsonResponse({'code':0,'msg':'只允许本地定时任务调用'})

        try:
            # 获取当前时间
            # now = datetime.now() + timedelta(hours=8)   #模拟utc+8
            now=datetime(2025,3,31,23,59,59)

            # 计算当月1号00:00:00的时间
            start_date = datetime(now.year, now.month, 1)
            # start_date = datetime(2024,10,20)

            #获取当月数据
            actions, df_action = get_story_action(start_date, now)
            tasks, df_nocomplete_task = get_nocomplete_task(start_date, now)
            finish_tasks, df_finish_task = get_finish_task(start_date, now)
            bugs, df_bug = get_bug(start_date, now)
            noclose_bugs, df_noclose_bug = get_noclose_bug(start_date, now)
            assigned_bugs, df_assigned_bug = get_assigned_bug(start_date, now)
            users, account2level, df_user = get_user()

            #计算得分
            task_done_percent = cal_task_done_percent(df_nocomplete_task,df_finish_task)
            bug_count = cal_bug_count(df_bug)
            story_change = cal_story_change(df_action)
            df = cal_story_create_review(df_action, df_user)

            #和用户表拼接
            #综合/咨询-任务完成率-得分，为空的就为0，为inf的为满分
            df = pd.merge(df, task_done_percent, on='account', how='left')
            df.loc[df['positionType']=='ask', '综合/咨询-任务指派数'] = df.loc[df['positionType']=='ask', '综合/咨询-任务指派数'].fillna(0)
            df.loc[df['positionType']=='ask', '综合/咨询-任务完成数'] = df.loc[df['positionType']=='ask', '综合/咨询-任务完成数'].fillna(0)
            df.loc[df['positionType']=='ask', '综合/咨询-任务完成率'] = df.loc[df['positionType']=='ask', '综合/咨询-任务完成率'].fillna(0).replace(np.inf, 100)
            df.loc[df['positionType']=='ask', '综合/咨询-任务完成率-得分'] = df.loc[df['positionType']=='ask', '综合/咨询-任务完成率-得分'].fillna(0).replace(np.inf, 100)
            # df['综合/咨询-任务完成率-得分'] = df['综合/咨询-任务完成率-得分'].fillna(0).astype(int)
            columns = list(df.columns)
            df_mid = df[df['positionType']=='ask']
            df_mid['综合/咨询-任务完成率-排名'] = df_mid.groupby('dept')['综合/咨询-任务完成率-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df_mid['综合/咨询-任务完成率-最大排名'] = df_mid.groupby('dept')['综合/咨询-任务完成率-排名'].transform('max')
            df = pd.merge(df, df_mid, on=columns, how='left')

            #实施-问题缺陷数-得分
            df = pd.merge(df, bug_count, on='account', how='left')
            df.loc[df['positionType']=='implement', '实施-问题缺陷数'] = df.loc[df['positionType']=='implement', '实施-问题缺陷数'].fillna(0)
            df.loc[df['positionType']=='implement', '实施-问题缺陷数-得分'] = df.loc[df['positionType']=='implement', '实施-问题缺陷数-得分'].fillna(0)
            # df['实施-问题缺陷数-得分'] = df['实施-问题缺陷数-得分'].fillna(0).astype(int)
            columns = list(df.columns)
            df_mid = df[df['positionType']=='implement']
            df_mid['实施-问题缺陷数-排名'] = df_mid.groupby('dept')['实施-问题缺陷数-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df_mid['实施-问题缺陷数-最大排名'] = df_mid.groupby('dept')['实施-问题缺陷数-排名'].transform('max')
            df = pd.merge(df, df_mid, on=columns, how='left')

            #需求-需求变更数-得分
            df = pd.merge(df, story_change, on='account', how='left')
            df.loc[df['positionType']=='story', '需求-需求变更数'] = df.loc[df['positionType']=='story', '需求-需求变更数'].fillna(0)
            df.loc[df['positionType']=='story', '需求-需求变更数-得分'] = df.loc[df['positionType']=='story', '需求-需求变更数-得分'].fillna(100)
            # df['需求-需求变更数-得分'] = df['需求-需求变更数-得分'].fillna(0).astype(int)
            columns = list(df.columns)
            df_mid = df[df['positionType']=='story']
            df_mid['需求-需求变更数-排名'] = df_mid.groupby('dept')['需求-需求变更数-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df_mid['需求-需求变更数-最大排名'] = df_mid.groupby('dept')['需求-需求变更数-排名'].transform('max')
            df = pd.merge(df, df_mid, on=columns, how='left')


            #通用指标
            general_task_done_score = general_task_done(finish_tasks, users)
            general_task_done_score = pd.DataFrame(general_task_done_score)
            # 删除列
            general_task_done_score = general_task_done_score.drop(['finishedBy'], axis=1)
            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            general_task_done_score = general_task_done_score.rename(columns={
                                                              'pri': '通用指标-优先级',
                                                              'difficulty': '通用指标-难度系数',
                                                              'consumed': '通用指标-消耗工时',
                                                              'weighted_score': '通用指标-加权求和',
                                                              'rank': '通用指标-排名',
                                                              'score': '通用指标-得分'})
            #将完成任务数为空的置为0
            # print(general_task_done_score)
            # print(list(general_task_done_score['通用指标-任务完成数']))
            general_task_done_score['通用指标-任务完成数'] = general_task_done_score['通用指标-任务完成数'].fillna(0).replace('',0)
            # print(list(general_task_done_score['通用指标-任务完成数']))
            # 更新最大排名列
            general_task_done_score['通用指标-最大排名'] = general_task_done_score.groupby('dept')['通用指标-排名'].transform('max')
            df = pd.merge(df, general_task_done_score, on=['account','realname','company','dept','name','positionType'], how='left')

            #通用指标-加分
            df['通用指标-加分数'] = 0
            df['通用指标-加分-得分'] = df['通用指标-加分数'].map(lambda x: min(x,3) * 5)
            df['通用指标-加分-排名'] = df.groupby('dept')['通用指标-加分-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df['通用指标-加分-最大排名'] = df.groupby('dept')['通用指标-加分-排名'].transform('max')

            # 通用指标-加分
            df['通用指标-减分数'] = 0
            df['通用指标-减分-得分'] = df['通用指标-减分数'].map(lambda x: min(x, 3) * 5)
            df['通用指标-减分-排名'] = df.groupby('dept')['通用指标-减分-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df['通用指标-减分-最大排名'] = df.groupby('dept')['通用指标-减分-排名'].transform('max')

            #开发-提交有效代码数
            try:

                #读取映射表
                with open(CODE_ACCOUNT_MAP,'r') as f:
                    code_account_map = json.load(f)

                #代码行数获取
                with open('/media/977GB/virtualbox/code_0408.json','r') as f:
                    response = json.load(f)
                # try:
                #     response = requests.get(CODE_URL)
                #     response = json.loads(response.text)
                # except:
                #     response = {'code':1}

                #获取开发人员
                df_dev = df[df['positionType'] == 'develop']
                account2name = {}
                for account, name in zip(df_dev['account'], df_dev['realname']):
                    account2name[account] = name

                #如果code不为0，则报错
                if response['code'] != 0:
                    # 获取失败
                    df['开发-提交有效代码数'] = 0
                    df['开发-代码提交基准值D'] = 0
                else:

                    account_code = {}
                    for detail in response['data']:

                        #将研发环境的用户名替换为禅道用户名
                        if detail['username'] in code_account_map.keys():
                            account = code_account_map[detail['username']]
                        else:
                            account = detail['username']

                        #只统计用户名在禅道里的
                        if account in account2name.keys():

                            if account not in account_code.keys():
                                account_code[account] = detail['totals']
                            else:
                                account_code[account] += detail['totals']

                    #转为list
                    account_code_list = []
                    for key, value in account_code.items():
                        account_code_list.append([key,value])

                    #代码行数df
                    df_code = pd.DataFrame(columns=['account','开发-提交有效代码数'],data=account_code_list)

                    #用户名和姓名拼接
                    # df['account_name'] = df.apply(lambda row: pinjie(row['account'], row['realname']), axis=1)

                    #df添加用户名和姓名拼接的列
                    df = pd.merge(df, df_code, on=['account'],how='left')

                    #添加基准值
                    df['开发-代码提交基准值D'] = df['account'].map(account2level)
                    df['开发-代码提交基准值D'] = df['开发-代码提交基准值D'].map({'高级':15429.9641873278,'中级':12723.2612524462,'初级':10268.0420560748})

                    #空的填充为0
                    df['开发-提交有效代码数'] = df['开发-提交有效代码数'].fillna(0)
                    df['开发-代码提交基准值D'] = df['开发-代码提交基准值D'].fillna(0)

                    # 删除列
                    # df = df.drop(['account_name'], axis=1)

            except:

                #获取失败
                df['开发-提交有效代码数'] = 0
                df['开发-代码提交基准值D'] = 0
            df['开发-代码提交情况'] = df['开发-提交有效代码数'] / df['开发-代码提交基准值D']
            df['开发-代码提交情况'] = df['开发-代码提交情况'].fillna(0).replace(np.inf, 0)
            df['开发-提交有效代码数-得分'] = df['开发-代码提交情况'].map(develop_code)
            df['开发-提交有效代码数-排名'] = df.groupby('dept')['开发-提交有效代码数-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df['开发-提交有效代码数-最大排名'] = df.groupby('dept')['开发-提交有效代码数-排名'].transform('max')

            #运维-系统故障情况
            df['运维-系统故障次数'] = 0
            df['运维-系统故障情况-得分'] = df['运维-系统故障次数'].map(lambda x : max(100 - (x * 50), 0))
            df['运维-系统故障情况-排名'] = df.groupby('dept')['运维-系统故障情况-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df['运维-系统故障情况-最大排名'] = df.groupby('dept')['运维-系统故障情况-排名'].transform('max')

            #运维-系统中断情况
            df['运维-系统中断时长'] = 0
            df['运维-系统中断情况-得分'] = df['运维-系统中断时长'].map(lambda x : max(100 - (x * 50), 0))
            df['运维-系统中断情况-排名'] = df.groupby('dept')['运维-系统中断情况-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df['运维-系统中断情况-最大排名'] = df.groupby('dept')['运维-系统中断情况-排名'].transform('max')

            #未关闭缺陷数
            not_close_bug_score = not_close_bug(assigned_bugs,users)
            not_close_bug_score = pd.DataFrame(not_close_bug_score)
            # 删除列
            not_close_bug_score = not_close_bug_score.drop(['submitted_bug','completed_bug','assignedTo','closed_bug'], axis=1)
            not_close_bug_score = not_close_bug_score.rename(columns={
                                                              'uncompleted_bug': '开发-未关闭缺陷数-未关闭缺陷数',
                                                              'rank': '开发-未关闭缺陷数-排名',
                                                              'score': '开发-未关闭缺陷数-得分'})
            # 更新最大排名列
            not_close_bug_score['开发-未关闭缺陷数-最大排名'] = not_close_bug_score.groupby('dept')['开发-未关闭缺陷数-排名'].transform('max')
            df = pd.merge(df, not_close_bug_score, on=['account', 'realname', 'company', 'dept', 'name', 'positionType'],
                          how='left')

            #提交缺陷数
            submmit_bug_score = submmit_bug(bugs,users)
            submmit_bug_score = pd.DataFrame(submmit_bug_score)
            # 删除列
            submmit_bug_score = submmit_bug_score.drop(['uncompleted_bug', 'completed_bug', 'assignedTo', 'closed_bug'], axis=1)
            submmit_bug_score = submmit_bug_score.rename(columns={'submitted_bug': '测试-提交缺陷数-提交缺陷数',
                                                                      'rank': '测试-提交缺陷数-排名',
                                                                      'score': '测试-提交缺陷数-得分'})
            # 更新最大排名列
            submmit_bug_score['测试-提交缺陷数-最大排名'] = submmit_bug_score.groupby('dept')['测试-提交缺陷数-排名'].transform('max')
            df = pd.merge(df, submmit_bug_score, on=['account', 'realname', 'company', 'dept', 'name', 'positionType'],
                          how='left')

            #缺陷闭环率
            bug_close_percent_score = bug_close_percent(noclose_bugs,users)
            bug_close_percent_score = pd.DataFrame(bug_close_percent_score)
            bug_close_percent_score['测试-缺陷闭环率-得分'] = bug_close_percent_score['score']
            bug_close_percent_score = bug_close_percent_score.rename(columns={'submitted_bug': '测试-缺陷闭环率-提交缺陷数',
                                                                              'closed_bug': '测试-缺陷闭环率-关闭缺陷数',
                                                                              'score': '测试-缺陷闭环率'})

            bug_close_percent_score['测试-缺陷闭环率-排名'] = bug_close_percent_score.groupby('dept')['测试-缺陷闭环率-得分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            bug_close_percent_score['测试-缺陷闭环率-最大排名'] = bug_close_percent_score.groupby('dept')['测试-缺陷闭环率-排名'].transform('max')
            df = pd.merge(df, bug_close_percent_score, on=['account', 'realname', 'company', 'dept', 'name', 'positionType'],
                          how='left')

            df['dept'] = df['dept'].replace('财务经营部', '计划财务部')

            #计算总分
            df['总分'] = None

            #开发人员
            df.loc[df['positionType']=='develop', '总分'] = \
                df.loc[df['positionType']=='develop', '通用指标-得分'] * 0.3 + \
                df.loc[df['positionType']=='develop', '通用指标-加分-得分'] * 0.15 - \
                df.loc[df['positionType']=='develop', '通用指标-减分-得分'] * 0.15 + \
                df.loc[df['positionType']=='develop', '开发-提交有效代码数-得分'] * 0.3 + \
                df.loc[df['positionType']=='develop', '开发-未关闭缺陷数-得分'] * 0.25

            #测试人员
            df.loc[df['positionType']=='test', '总分'] = \
                df.loc[df['positionType']=='test', '通用指标-得分'] * 0.3 + \
                df.loc[df['positionType']=='test', '通用指标-加分-得分'] * 0.15 - \
                df.loc[df['positionType']=='test', '通用指标-减分-得分'] * 0.15 + \
                df.loc[df['positionType']=='test', '测试-提交缺陷数-得分'] * 0.25 + \
                df.loc[df['positionType']=='test', '测试-缺陷闭环率-得分'] * 0.3

            #需求人员
            df.loc[df['positionType']=='story', '总分'] = \
                df.loc[df['positionType']=='story', '通用指标-得分'] * 0.3 + \
                df.loc[df['positionType']=='story', '通用指标-加分-得分'] * 0.15 - \
                df.loc[df['positionType']=='story', '通用指标-减分-得分'] * 0.15 + \
                df.loc[df['positionType']=='story', '需求-工作活跃度-得分'] * 0.35 + \
                df.loc[df['positionType']=='story', '需求-需求变更数-得分'] * 0.2

            #运维人员
            df.loc[df['positionType']=='om', '总分'] = \
                df.loc[df['positionType']=='om', '通用指标-得分'] * 0.3 + \
                df.loc[df['positionType']=='om', '通用指标-加分-得分'] * 0.15 - \
                df.loc[df['positionType']=='om', '通用指标-减分-得分'] * 0.15 + \
                df.loc[df['positionType']=='om', '运维-系统故障情况-得分'] * 0.3 + \
                df.loc[df['positionType']=='om', '运维-系统中断情况-得分'] * 0.25

            #实施人员
            df.loc[df['positionType']=='implement', '总分'] = \
                df.loc[df['positionType']=='implement', '通用指标-得分'] * 0.3 + \
                df.loc[df['positionType']=='implement', '通用指标-加分-得分'] * 0.15 - \
                df.loc[df['positionType']=='implement', '通用指标-减分-得分'] * 0.15 + \
                df.loc[df['positionType']=='implement', '实施-问题缺陷数-得分'] * 0.55

            #咨询/综合人员
            df.loc[df['positionType']=='ask', '总分'] = \
                df.loc[df['positionType']=='ask', '通用指标-得分'] * 0.3 + \
                df.loc[df['positionType']=='ask', '通用指标-加分-得分'] * 0.15 - \
                df.loc[df['positionType']=='ask', '通用指标-减分-得分'] * 0.15 + \
                df.loc[df['positionType']=='ask', '综合/咨询-任务完成率-得分'] * 0.55

            # 在每个部门内按总分进行排名，相同值时排名相同
            df['岗位-排名'] = df.groupby(['dept','positionType'])['总分'].rank(method='dense', ascending=False)
            # 在每个部门内按总分进行排名，相同值时排名相同
            df['岗位-最大排名'] = df.groupby(['dept', 'positionType'])['岗位-排名'].transform('max')
            # 在每个部门内按总分进行排名，相同值时排名相同
            df['排名'] = df.groupby('dept')['总分'].rank(method='dense', ascending=False)
            # 更新最大排名列
            df['最大排名'] = df.groupby('dept')['排名'].transform('max')

            #只筛选打分的岗位
            df = df[~df['name'].isin(['项目经理', '产品经理', '部门管理员', '项目管理员'])]

            #转换列名
            df = df.rename(columns={'account': '用户名',
                                  'realname': '姓名',
                                  'company': '公司',
                                    'dept': '部门',
                                    'name': '岗位',
                                    'positionType': '岗位类型',})

            _type_map = {'general': '通用',
                         'develop': '开发',
                         'test': '测试',
                         'story': '需求',
                         'om': '运维',
                         'implement': '实施',
                         'ask': '综合/咨询'}

            # df['岗位类型'] = df['岗位类型'].map(_type_map)
            #只要有公司的
            # df = df[~df['公司'].isna()]
            # df.to_excel('得分.xlsx', index=False)
            # print(df.head())
            # time.sleep(1000000)
            insert_mysql_ls = []
            # 开始结束日期
            st_date = start_date.date()
            ed_date = now.date()

            #查询上一次版本号
            last_record = ZtRdEfficiency.objects.last()

            if last_record:
                if last_record.endDate.month == ed_date.month:
                    version = last_record.version + 1
                else:
                    version = 1
            else:
                version = 1

            for row in df.itertuples():
                detail = {
                    #通用-任务完成数
                    "general_task_done_pri": row[25].split('.')[-1],
                    "general_task_done_difficulty":row[26],
                    "general_task_done_consumed": row[27],
                    "general_task_done_counts": int(row[28]),
                    "general_task_done_sum": row[29],
                    "general_task_done_rank": f'{int(row[30])}/{int(row[32])}',
                    "general_task_done_score": int(row[31]),
                    "general_task_done_weight": 30,

                    #通用-加分项
                    "general_bonus_points": int(row[33]),
                    "general_bonus_desc": '',
                    # 计算规则
                    "general_bonus_rule": f'min({int(row[34])}, 3) * 5',
                    "general_bonus_score": int(row[34]),
                    "general_bonus_rank": f'{int(row[35])}/{int(row[36])}',

                    #通用-减分项
                    "general_subtract_points": int(row[37]),
                    "general_subtract_desc": '',
                    # 计算规则
                    "general_subtract_rule": f'{int(row[38])} * 5',
                    "general_subtract_score": int(row[38]),
                    "general_subtract_rank": f'{int(row[39])}/{int(row[40])}',

                    "general_bonus_subtract_weight": 15,

                    #通用总分
                    "general_score": 0.3*int(row[31]) + 0.15*int(row[34]) - 0.15*int(row[38])
                }

                #开发
                if row[5] in ['开发组长', '开发', '开发负责人']:

                    #开发代码
                    detail['develop_code'] =int(row[41])
                    detail['develop_code_benchmark'] = float(row[42])
                    detail['develop_code_percent'] = float(row[43])
                    # 计算规则
                    if float(row[43])==0:
                        detail['develop_code_percent_rule'] = f'未提交或者代码量为0得0分'
                    elif 0<float(row[43])<0.5:
                        detail['develop_code_percent_rule'] = f'50 + 20 * {float(row[43])}'
                    elif 0.5<=float(row[43])<1:
                        detail['develop_code_percent_rule'] = f'60 + 20 * ({float(row[43])} - 0.5)'
                    elif 1<=float(row[43])<1.5:
                        detail['develop_code_percent_rule'] = f'70 + 20 * ({float(row[43])} - 1)'
                    elif 1.5<=float(row[43])<2:
                        detail['develop_code_percent_rule'] = f'80 + 40 * ({float(row[43])} - 1.5)'
                    else:
                        detail['develop_code_percent_rule'] = f'代码提交情况达到基准值2倍及以上得分=100'

                    detail['develop_code_score'] = float(row[44])
                    detail['develop_code_rank'] = f'{int(row[45])}/{int(row[46])}'
                    detail['develop_code_weight'] = 30

                    #开发未关闭缺陷数
                    # detail['develop_not_bug_assigns'] = int(row[42])
                    # detail['develop_not_bug_noresolves'] = int(row[43])
                    detail['develop_not_bug_no_closes'] = int(row[55])
                    detail['develop_not_bug_rank'] = f'{int(row[56])}/{int(row[58])}'
                    detail['develop_not_bug_score'] = int(row[57])
                    detail['develop_not_bug_weight'] = 25

                    # 开发总分
                    detail['develop_score'] = 0.3*float(row[44]) + 0.25*int(row[57])

                elif row[5] in ['测试负责人', '测试组长', '测试']:

                    # 测试缺陷数
                    detail['test_bug_submmit'] = int(row[59])
                    detail['test_bug_rank'] = f'{int(row[60])}/{int(row[62])}'
                    detail['test_bug_score'] = int(row[61])
                    detail['test_bug_weight'] = 25

                    # 测试缺陷闭环率
                    detail['test_bug_close_submmit'] = int(row[63])
                    detail['test_bug_close_close'] = int(row[64])
                    # 计算规则
                    detail['test_bug_close_percent_rule'] = f'({int(row[64])} / {int(row[63])}) * 100'
                    detail['test_bug_close_percent'] = float(row[65])
                    detail['test_bug_close_score'] = float(row[66])
                    detail['test_bug_close_rank'] = f'{int(row[67])}/{int(row[68])}'
                    detail['test_bug_close_weight'] = 30

                    # 测试总分
                    detail['test_score'] = 0.25*int(row[61]) + 0.3*float(row[66])

                elif row[5] in ['需求', '需求负责人', '需求组长']:

                    # 需求活跃度
                    detail['story_active_counts'] = int(row[7])
                    detail['story_active_rank'] = f'{int(row[8])}/{int(row[9])}'
                    detail['story_active_score'] = int(row[10])
                    detail['story_active_weight'] = 35

                    # 需求变更数
                    detail['story_change_counts'] = int(row[21])
                    detail['story_change_score'] = int(row[22])
                    detail['story_change_rank'] = f'{int(row[23])}/{int(row[24])}'
                    detail['story_change_weight'] = 20

                    # 需求总分
                    detail['story_score'] = 0.35*int(row[10]) + 0.2*int(row[22])

                elif row[5] in ['运维']:

                    # 运维故障数
                    detail['om_fault_counts'] = int(row[47])
                    # 计算规则
                    detail['om_fault_rule'] = f'max(100 - ({int(row[47])} * 50), 0)'
                    detail['om_fault_score'] = int(row[48])
                    detail['om_fault_rank'] = f'{int(row[49])}/{int(row[50])}'
                    detail['om_fault_weight'] = 30

                    # 运维中断数
                    detail['om_interrupt_counts'] = int(row[51])
                    # 计算规则
                    detail['om_interrupt_rule'] = f'max(100 - ({int(row[51])} * 50), 0)'
                    detail['om_interrupt_score'] = int(row[52])
                    detail['om_interrupt_rank'] = f'{int(row[53])}/{int(row[54])}'
                    detail['om_interrupt_weight'] = 25

                    # 运维总分
                    detail['om_score'] = 0.3*int(row[48]) + 0.25*int(row[52])

                elif row[5] in ['实施']:

                    # 实施问题缺陷数
                    detail['implement_bug_counts'] = int(row[17])
                    # 计算规则
                    detail['implement_bug_rule'] = f'min({int(row[17])} * 25, 100)'
                    detail['implement_bug_score'] = int(row[18])
                    detail['implement_bug_rank'] = f'{int(row[19])}/{int(row[20])}'
                    detail['implement_bug_weight'] = 55

                    # 实施总分
                    detail['implement_score'] = int(row[18]) *0.55

                elif row[5] in ['综合人员', '咨询人员']:

                    # 综合/咨询问题缺陷数
                    detail['ask_task_assigns'] = int(row[12])
                    detail['ask_task_dones'] = int(row[11])
                    #计算规则
                    detail['ask_task_percent_rule'] = f'({int(row[11])} / {int(row[12])}) * 100'
                    detail['ask_task_percent'] = float(row[13])
                    detail['ask_task_score'] = float(row[14])
                    detail['ask_task_rank'] = f'{int(row[15])}/{int(row[16])}'
                    detail['ask_task_weight'] = 55

                    # 综合/咨询总分
                    detail['ask_score'] = float(row[14]) * 0.55

                detail = json.dumps(detail, ensure_ascii=False)

                insert_mysql_ls.append((row[1],row[2],row[4],row[3],row[5],row[6],detail,row[-5],row[-4],row[-3],row[-2],row[-1],st_date,ed_date,now,version,'1'))

            # 建立数据库连接
            try:
                # pymysql连接数据
                connection = pymysql.connect(**PYMYSQL_CONF)
            except:
                print(traceback.format_exc())
                return JsonResponse({'code': 0, 'msg': "无法连接到数据库"}, status=200)
            try:

                # 创建游标对象
                with connection.cursor() as cursor:

                    # 先将数据库中表示改为旧的
                    sql = """
                        UPDATE `zt_rdefficiency`
                        SET `latest` = %s
                        WHERE `latest` = %s
                        """
                    values = ('0', '1')
                    # 执行 SQL 语句
                    cursor.execute(sql, values)

                    # 提交事务
                    connection.commit()

                    # 准备批量插入的SQL语句
                    sql = """
                    INSERT INTO zt_rdefficiency (account, realname, dept, company, position, positionType, detail, score, 
                    positionRank, positionMaxRank, rank, maxRank, startDate, endDate, createDate, version, latest)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    """
                    # 执行批量插入
                    cursor.executemany(sql, insert_mysql_ls)
                    # 提交事务
                    connection.commit()
            except pymysql.MySQLError as e:
                print(f"Error: {e}")
                # 发生错误时回滚
                connection.rollback()
            finally:
                # 关闭数据库连接
                connection.close()

            res = {
                'code': 1,
                'msg': "计算成功"
            }
            return JsonResponse(res)
        except:
            res = {
                'code': 0,
                'msg': '计算失败'
            }
            print(traceback.format_exc())
            return JsonResponse(res)

    #不是post请求
    res = {
        'code': 0,
        'msg': "不支持的请求方法"
    }
    return JsonResponse(res, status=405)

#获取用户得分
@timing_decorator
def get_user_score(request):
    if request.method == 'GET':

        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            return JsonResponse({'code': 0, 'msg': '未登录'})

        # 获取参数
        account = request.GET.get('account')

        if not account:
            account = request.session['account']

        score = ZtRdEfficiency.objects.filter(account=account, latest='1').distinct().first()

        if not score:
            res = {
                'code': 0,
                'msg': "没有该用户的研发效能评分"
            }
            return JsonResponse(res)

        position_map = {
            '咨询人员': '综合/咨询',
            '综合人员': '综合/咨询',
            '需求负责人': '需求',
            '需求组长': '需求',
            '测试负责人': '测试',
            '测试组长': '测试',
            '开发负责人': '开发',
            '开发组长': '开发'
        }

        # #转换岗位
        if score.position in position_map.keys():
            position = position_map[score.position]
        else:
            position = score.position

        res = {
            'code': 1,
            'msg': "获取成功",
            'data':{
                'title': f'{score.endDate} {score.realname}的研发效能',
                'id': score.id,
                'account': score.account,
                'realname': score.realname,
                'dept': score.dept,
                'company': score.company,
                'position': score.position,
                'position_type': position,
                'detail': json.loads(score.detail),
                'score': score.score,
                'positionRank': score.positionRank,
                'positionMaxRank': score.positionMaxRank,
                'rank': score.rank,
                'max_rank': score.maxRank,
                'startDate': score.startDate,
                'endDate': score.endDate,
                'version': score.version
            }
        }

        return JsonResponse(res)
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#获取研发效能规则
@timing_decorator
def get_rule(request):
    if request.method == 'GET':

        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            return JsonResponse({'code': 0, 'msg': '未登录'})

        # 获取参数
        _type = request.GET.get('type')

        # 未填写
        if not _type:
            res = {
                'code': 0,
                'msg': '未填写参数'
            }
            return JsonResponse(res)

        # _type_map = {'general': '通用',
        #              'develop': '开发',
        #              'test': '测试',
        #              'story': '需求',
        #              'om': '运维',
        #              'implement': '实施',
        #              'ask': '综合/咨询'}

        # 未填写
        _types = ['general','develop','test','story','om','implement','ask']
        if _type not in _types:
            res = {
                'code': 0,
                'msg': f'只支持: {"、".join(_types)}'
            }
            return JsonResponse(res)

        rule = ZtRdEfficiencyRule.objects.filter(latest='1',type=_type).values('rule').distinct().first()

        if not rule:
            res = {
                'code': 0,
                'msg': '没有该规则'
            }
            return JsonResponse(res)

        res = {
            'code': 1,
            'msg': "获取成功",
            'data': rule['rule']
        }

        return JsonResponse(res)
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

# # 创建自定义分页类
# class CustomPageNumberPagination(PageNumberPagination):
#     page_size = 10  # 默认每页显示10条数据
#     page_size_query_param = 'page_size'  # 允许客户端通过查询参数 page_size 来指定每页大小
#     max_page_size = 100  # 限制客户端通过 page_size 查询参数设置的最大页大小

#获取部门所有得分
class ZtRdEfficiencyDeptViewSet(viewsets.ModelViewSet):
    queryset = ZtRdEfficiency.objects
    serializer_class = ZtRdEfficiencySerializer

    def initial(self, request, *args, **kwargs):
        super(ZtRdEfficiencyDeptViewSet, self).initial(request, *args, **kwargs)
        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            raise AuthenticationFailed({'code': 0, 'msg': '未登录'})

    # 格式化数据
    @timing_decorator
    def list(self, request, *args, **kwargs):
        try:

            # 获取参数
            dept = request.session['dept']

            if not dept:
                return Response({'code': 0, 'msg': '该人员没有部门'})

            # 搜索参数
            name = request.GET.get('name')
            _type = request.GET.get('type')
            endDate = request.GET.get('endDate')

            #页码参数
            page = request.GET.get('page')
            page_size = request.GET.get('page_size')

            if not page or not page_size:
                return Response({'code': 0, 'msg': '未携带page或者page_size'})

            #查询条件
            # 搜索条件
            query = Q()
            query &= Q(dept=dept)  # 部门
            # query &= Q(latest='1')  # 最新的评分
            if name:
                query &= Q(realname__contains=name)  # 姓名
            if _type:
                query &= Q(positionType=_type)  # 岗位类型
            if endDate:
                query &= Q(endDate=endDate)  # 统计日期
            else:
                query &= Q(latest='1')  # 最新的评分

            # 查询
            #.values('id', 'realname', 'positionType', 'score', 'rank')
            queryset = self.queryset.filter(query).values('id', 'account', 'realname','positionType', 'dept', 'detail',
                                                          'score','positionRank','positionMaxRank', 'rank','maxRank', 'endDate', 'version').distinct()

            paginator = Paginator(queryset, page_size)
            page_queryset = paginator.get_page(page)

            # 未查询到
            if not queryset.exists():
                return Response({'code': 0, 'msg': '没有记录'})

            serializer = self.get_serializer(page_queryset, many=True)

            return Response({'code': 1, 'msg': '获取成功', 'data': serializer.data, 'page_num':paginator.num_pages, 'total':len(queryset), 'title': f'{serializer.data[0]["endDate"]} {dept}的研发效能'})
        except:
            print(traceback.format_exc())
            return Response({'code': 0, 'msg': '获取失败'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

#获取研发效能部门统计图
@timing_decorator
def get_dept_analyse(request):
    if request.method == 'GET':

        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            return JsonResponse({'code': 0, 'msg': '未登录'})

        # 获取参数
        dept = request.session['dept']

        if not dept:
            return JsonResponse({'code': 0, 'msg': '该人员没有部门'})

        sql = f'''
        SELECT 
        positionType,
        COUNT(*) AS positionCount,  -- 统计各岗位类型的人数
        AVG(score) AS averageScore  -- 计算各岗位类型的平均分
        FROM 
            zt_rdefficiency  -- 替换为你的表名
        WHERE 
            latest = '1' AND dept = '{dept}'  -- 筛选latest为1的记录
        GROUP BY 
            positionType;  -- 按岗位类型分组
        '''

        # pymysql连接数据
        connection = pymysql.connect(**PYMYSQL_CONF)
        # 执行 SQL 语句
        # 连接数据库
        with connection.cursor() as cursor:
            cursor.execute(sql)
            data = cursor.fetchall()

        #映射
        type_map = {'develop': '开发',
                     'test': '测试',
                     'story': '需求',
                     'om': '运维',
                     'implement': '实施',
                     'ask': '综合/咨询'}

        res_data = {'counts':[],'avg_score':[]}
        for row in data:
            res_data['counts'].append({type_map[row[0]]:row[1]})
            res_data['avg_score'].append({type_map[row[0]]: row[2]})

        res = {
            'code': 1,
            'msg': "获取成功",
            'data': res_data
        }

        return JsonResponse(res)
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#判断一个字符串是否包含字符
import re
def contains_keywords(text, keywords):
    for keyword in keywords:
        if re.search(keyword, text):
            return True
    return False

#获取字母对应的序号
def number_to_excel_column(n):
    result = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        result = chr(65 + remainder) + result
    return result

#获取研发效能部门统计excel
@timing_decorator
def get_rdEfficiency_excel(request):
    if request.method == 'GET':

        # # 检查用户是否登录
        # if not has_permission(request):
        #     # 如果用户未登录，抛出 AuthenticationFailed 异常
        #     return JsonResponse({'code': 0, 'msg': '未登录'})

        # if not dept:
        #     return Response({'code': 0, 'msg': '该人员没有部门'})

        # 获取参数
        position_type = request.GET.get('position_type')
        dept_type = request.GET.get('dept_type')

        if not dept_type:
            return JsonResponse({'code': 0, 'msg': '未携带导出类型'})

        if dept_type not in ['dept', 'company']:
            return JsonResponse({'code': 0, 'msg': '导出类型只支持部门和公司'})

        query = Q(latest='1')
        if position_type:
            query &= Q(positionType=position_type)
        #类型为部门的话则筛选部门，否则为公司整个的
        if dept_type == 'dept':
            # 获取参数
            dept = request.session['dept']
            query &= Q(dept=dept)

        scores = ZtRdEfficiency.objects.filter(query).distinct()

        if not scores:
            res = {
                'code': 0,
                'msg': "没有该用户的研发效能评分"
            }
            return JsonResponse(res)

        #映射
        type_map = {'develop': '开发',
                     'test': '测试',
                     'story': '需求',
                     'om': '运维',
                     'implement': '实施',
                     'ask': '综合/咨询'}

        #名称映射
        name_en2zh = {
            "account": "用户名",
            "realname": "姓名",
            "dept": "部门",
            "company": "公司",
            "position": "岗位",
            "position_type": "岗位类型",
            "score": '总分',
            "positionRank": '岗位-排名',
            "rank": '部门-排名',
            "startDate": "统计开始日期",
            "endDate": "统计结束日期",
            "general_task_done_pri": "通用指标-任务完成数-优先级",
            "general_task_done_difficulty": "通用指标 - 难度系数",
            "general_task_done_consumed": "通用指标 - 消耗工时",
            "general_task_done_counts": '通用指标 - 任务完成数',
            "general_task_done_sum": '通用指标 - 加权求和',
            "general_task_done_rank": "通用指标 - 排名",
            "general_task_done_score": '通用指标 - 得分',
            "general_task_done_weight": '通用指标占比权重',
            "general_bonus_points": '通用指标 - 加分数',
            "general_bonus_desc": '加分项描述',
            "general_bonus_rule": "通用指标 - 加分数计算规则",
            "general_bonus_score": '通用指标 - 加分 - 得分',
            "general_bonus_rank": "通用指标 - 加分 - 排名",
            "general_subtract_points": '通用指标 - 减分数',
            "general_subtract_desc": '减分项描述',
            "general_subtract_rule": "通用指标 - 减分数计算规则",
            "general_subtract_score": '通用指标 - 减分 - 得分',
            "general_subtract_rank": "通用指标 - 减分 - 排名",
            "general_bonus_subtract_weight": '通用指标加分减分共占比权重',
            "general_score": '通用总分',

            "ask_task_assigns": '综合 / 咨询 - 任务指派数',
            "ask_task_dones": '综合 / 咨询 - 任务完成数',
            "ask_task_percent_rule": '综合 / 咨询 - 任务完成率计算规则',
            "ask_task_percent": '综合 / 咨询 - 任务完成率',
            "ask_task_score": '综合 / 咨询 - 任务完成率 - 得分',
            "ask_task_rank": "综合 / 咨询 - 任务完成率 - 排名",
            "ask_task_weight": '综合 / 咨询 - 任务完成率占比权重',
            "ask_score": '综合 / 咨询总分',

            "develop_code": '开发 - 提交有效代码数',
            "develop_code_benchmark": '开发 - 代码提交基准值D',
            "develop_code_percent": '开发 - 代码提交情况',
            "develop_code_percent_rule": "开发 - 代码提交情况计算规则",
            "develop_code_score": '开发 - 提交有效代码数 - 得分',
            "develop_code_rank": "开发 - 提交有效代码数 - 排名",
            "develop_code_weight": '开发 - 提交有效代码数占比权重',
            "develop_not_bug_no_closes": '开发 - 未关闭缺陷数 - 关闭缺陷数',
            "develop_not_bug_rank": "开发 - 未关闭缺陷数 - 排名",
            "develop_not_bug_score": '开发 - 未关闭缺陷数 - 得分',
            "develop_not_bug_weight": '开发 - 未关闭缺陷数占比权重',
            "develop_score": '开发总分',

            "test_bug_submmit": '测试 - 提交缺陷数 - 提交缺陷数',
            "test_bug_rank": "测试 - 提交缺陷数 - 排名",
            "test_bug_score": '测试 - 提交缺陷数 - 得分',
            "test_bug_weight": '测试 - 提交缺陷数占比权重',
            "test_bug_close_submmit": '测试 - 缺陷闭环率 - 提交缺陷数',
            "test_bug_close_close": '测试 - 缺陷闭环率 - 关闭缺陷数',
            "test_bug_close_percent_rule": "测试 - 缺陷闭环率计算规则",
            "test_bug_close_percent": '测试 - 缺陷闭环率',
            "test_bug_close_score": '测试 - 缺陷闭环率 - 得分',
            "test_bug_close_rank": "测试 - 缺陷闭环率 - 排名",
            "test_bug_close_weight": '测试 - 缺陷闭环率占比权重',
            "test_score": '测试总分',

            "story_active_counts": '需求创建评审数',
            "story_active_rank": "需求 - 工作活跃度 - 排名",
            "story_active_score": '需求 - 工作活跃度 - 得分',
            "story_active_weight": '需求 - 工作活跃度占比权重',
            "story_change_counts": '需求 - 需求变更数',
            "story_change_score": '需求 - 需求变更数 - 得分',
            "story_change_rank": '需求 - 需求变更数 - 排名',
            "story_change_weight": '需求 - 需求变更数占比权重',
            "story_score": '需求总分数',

            "om_fault_counts": '运维 - 系统故障次数',
            "om_fault_rule": "运维 - 系统故障次数计算规则",
            "om_fault_score": '运维 - 系统故障情况 - 得分',
            "om_fault_rank": "运维 - 系统故障情况 - 排名",
            "om_fault_weight": '运维 - 系统故障情况占比权重',
            "om_interrupt_counts": '运维 - 系统中断时长',
            "om_interrupt_rule": "运维 - 系统中断时长计算规则",
            "om_interrupt_score": '运维 - 系统中断情况 - 得分',
            "om_interrupt_rank": "运维 - 系统中断情况 - 排名",
            "om_interrupt_weight": '运维 - 系统中断情况占比权重',
            "om_score": '运维总分数',

            "implement_bug_counts": '实施 - 问题缺陷数',
            "implement_bug_rule": "实施 - 问题缺陷数计算规则",
            "implement_bug_score": '实施 - 问题缺陷数 - 得分',
            "implement_bug_rank": "实施 - 问题缺陷数 - 排名",
            "implement_bug_weight": '实施 - 问题缺陷数占比权重',
            "implement_score": '实施总分数'
        }

        thin_border = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))

        data = {}
        for score in scores:

            positionType = type_map[score.positionType]

            score_dict = {
                'account': score.account,
                'realname': score.realname,
                'dept': score.dept,
                'company': score.company,
                'position': score.position,
                'position_type': positionType,
            }

            #将detail合并到总dict中
            score_dict.update(json.loads(score.detail))

            score_dict['score'] = score.score
            score_dict['positionRank'] = score.positionRank
            score_dict['rank'] = score.rank
            score_dict['startDate'] = score.startDate
            score_dict['endDate'] = score.endDate

            if positionType not in data:
                data[positionType] = []
                ls = {}
                for key in score_dict.keys():
                    ls[key] = name_en2zh[key]
                data[positionType].append(ls)

            data[positionType].append(score_dict)

        #将岗位类型列转为中文
        # df['position_type'] = df['position_type'].map(type_map)


        # 设置文件名为中文，并进行URL编码
        if dept_type == 'dept':
            if position_type:
                filename = f"{dept}-{type_map[position_type]}-研发效能.xlsx"
            else:
                filename = f"{dept}-研发效能.xlsx"
        else:
            if position_type:
                filename = f"公司-{type_map[position_type]}-研发效能.xlsx"
            else:
                filename = f"公司-研发效能.xlsx"
        encoded_filename = iri_to_uri(filename)

        # 创建一个Excel writer，直接写入到HTTP response的输出流中
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = f'attachment; filename="{encoded_filename}"'

        # 使用io.BytesIO作为临时存储，并指定引擎为xlsxwriter
        with pd.ExcelWriter(response, engine='openpyxl') as writer:

            for key,value in data.items():
                # 转为df
                df = pd.DataFrame(value)
                df = df.rename(columns=name_en2zh)

                # # 筛选需要的列
                # filter_columns = []
                # columns = list(df.columns)
                # for column in columns[:-5]:
                #     if contains_keywords(column, ['得分', '占比权重', '总分'] + list(name_en2zh.values())[:6]):
                #         filter_columns.append(column)
                #
                # df = df[filter_columns + columns[-5:]]
                row_len, columns_len = df.shape[:2]

                columns = list(df.columns)
                index = columns.index('通用总分')

                df.to_excel(writer, index=False, sheet_name=key.replace('综合/咨询','综合_咨询'))

                #合并单元格
                # 获取工作表
                worksheet = writer.sheets[key.replace('综合/咨询','综合_咨询')]

                # 纵向合并单元格
                for i in list(range(1,7)) + list(range(columns_len-4,columns_len+1)):
                    _char = number_to_excel_column(i)
                    worksheet.merge_cells(f'{_char}1:{_char}2')

                    # 将样式应用到合并的单元格
                    worksheet[F'{_char}1'].alignment = Alignment(horizontal='center', vertical='center')

                # 横向合并
                st_char = number_to_excel_column(7)
                end_char = number_to_excel_column(index+1)
                # 合并单元格，这里我们合并第一行的前两列
                worksheet.merge_cells(f'{st_char}1:{end_char}1')

                # 你也可以设置合并后的单元格的值
                worksheet[F'{st_char}1'] = '通用'

                st_char = number_to_excel_column(index+2)
                end_char = number_to_excel_column(columns_len-5)
                # 合并单元格，这里我们合并第一行的前两列
                worksheet.merge_cells(f'{st_char}1:{end_char}1')

                # 你也可以设置合并后的单元格的值
                worksheet[F'{st_char}1'] = key.replace('综合/咨询','综合_咨询')

                #画框
                area = worksheet[f'$A$1:${number_to_excel_column(columns_len)}${row_len+1}']
                for k in area:
                    for h in k:
                        h.border = thin_border

        return response

    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#获取公司所有得分
class ZtRdEfficiencyCompanyViewSet(viewsets.ModelViewSet):
    queryset = ZtRdEfficiency.objects
    serializer_class = ZtRdEfficiencySerializer

    def initial(self, request, *args, **kwargs):
        super(ZtRdEfficiencyCompanyViewSet, self).initial(request, *args, **kwargs)
        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            raise AuthenticationFailed({'code': 0, 'msg': '未登录'})

    # 格式化数据
    @timing_decorator
    def list(self, request, *args, **kwargs):
        try:

            # 搜索参数
            name = request.GET.get('name')
            _type = request.GET.get('type')
            dept = request.GET.get('dept')
            endDate = request.GET.get('endDate')

            #页码参数
            page = request.GET.get('page')
            page_size = request.GET.get('page_size')

            #查询条件
            # 搜索条件
            query = Q()
            if dept:
                query &= Q(dept__contains=dept)  # 部门
            if name:
                query &= Q(realname__contains=name)  # 姓名
            if _type:
                query &= Q(positionType=_type)  # 岗位类型
            if endDate:
                query &= Q(endDate=endDate)  # 统计日期
            else:
                query &= Q(latest='1')  # 最新的评分

            # 查询
            #.values('id', 'realname', 'positionType', 'score', 'rank')
            queryset = self.queryset.filter(query).values('id', 'account', 'realname','positionType', 'dept', 'detail'
                                                          ,'positionRank','positionMaxRank','score', 'rank','maxRank', 'endDate', 'version').distinct()

            paginator = Paginator(queryset, page_size)
            page_queryset = paginator.get_page(page)

            # 未查询到
            if not queryset.exists():
                return Response({'code': 0, 'msg': '没有记录'})

            serializer = self.get_serializer(page_queryset, many=True)

            return Response({'code': 1, 'msg': '获取成功', 'data': serializer.data, 'page_num':paginator.num_pages, 'total':len(queryset), 'title': f'{serializer.data[0]["endDate"]} 各事业部研发效能'})
        except:
            print(traceback.format_exc())
            return Response({'code': 0, 'msg': '获取失败'}, status=status.HTTP_500_INTERNAL_SERVER_ERROR)


#获取研发效能公司统计图
@timing_decorator
def get_company_analyse(request):
    if request.method == 'GET':

        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            return JsonResponse({'code': 0, 'msg': '未登录'})

        sql = f'''
        SELECT 
        dept,
        COUNT(*) AS positionCount,  -- 统计各岗位类型的人数
        AVG(score) AS averageScore  -- 计算各岗位类型的平均分
        FROM 
            zt_rdefficiency  -- 替换为你的表名
        WHERE 
            latest = '1'  -- 筛选latest为1的记录
        GROUP BY 
            dept;  -- 按岗位类型分组
        '''

        # pymysql连接数据
        connection = pymysql.connect(**PYMYSQL_CONF)
        # 执行 SQL 语句
        # 连接数据库
        with connection.cursor() as cursor:
            cursor.execute(sql)
            data = cursor.fetchall()

        res_data = {'counts':[],'avg_score':[]}
        for row in data:
            res_data['counts'].append({row[0]:row[1]})
            res_data['avg_score'].append({row[0]: row[2]})

        res = {
            'code': 1,
            'msg': "获取成功",
            'data': res_data
        }

        return JsonResponse(res)
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)

#获取研发效能公司部门
@timing_decorator
def get_company_dept(request):
    if request.method == 'GET':

        # 检查用户是否登录
        if not has_permission(request):
            # 如果用户未登录，抛出 AuthenticationFailed 异常
            return JsonResponse({'code': 0, 'msg': '未登录'})

        depts = ZtDept.objects.all()

        all_dept = []

        for dept in depts:
            # if dept.name in ['test部门','test部门','test部门','test部门','test部门','test部门']:
            #     continue
            all_dept.append(dept.name)

        res = {
            'code': 1,
            'msg': "获取成功",
            'data': all_dept
        }

        return JsonResponse(res)
    else:
        res = {
            'code': 0,
            'msg': "不支持的请求方法"
        }
        return JsonResponse(res, status=405)
